﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace MDSAdministration
{
    public class DataHelper
    {
        //public static SqlConnection sqlConn;
        public static SqlCommand command;

        static public DataSet GetDataSchemaTest(string server, string database)
        {
            string sqlQuery = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES t  " +
                              "INNER JOIN INFORMATION_SCHEMA.COLUMNS c ON t.TABLE_NAME = c.TABLE_NAME AND t.TABLE_SCHEMA = c.TABLE_SCHEMA  " +
                              "WHERE t.TABLE_SCHEMA = 'mdm'  " +
                              "AND t.TABLE_NAME = 'tblEntity'  " +
                              "AND c.COLUMN_NAME = 'StagingBase'";

            return GetData(sqlQuery, server, database);
        }
        static public DataSet GetDataModelDetails(ServerMDSDetails server)
        {

            var dsResult = GetDataSchemaTest(server.ServerName, server.Database);
            bool result = false;
            if(dsResult.Tables.Count > 0)
            {
                result = Convert.ToBoolean(dsResult.Tables[0].Rows[0][0]);
            }

            string sqlQuery = String.Format("SELECT DISTINCT m.ID AS '" + Constants.MODEL_ID + "', " +
                       "m.Name AS '" + Constants.MODEL_NAME + "', " +
                       "m.IsSystem AS '" + Constants.MODEL_ISSYSTEM + "', " +
                       "e.ID AS '" + Constants.ENTITY_ID + "', " +
                       "e.Name AS '" + Constants.ENTITY_NAME + "', " +
                       "CAST(e.MUID AS NVARCHAR(50)) AS '" + Constants.ENTITY_MUID + "', " +
                       "e.IsSystem AS '" + Constants.ENTITY_ISSYSTEM + "', " +
                       "e.EntityTable AS '" + Constants.ENTITY_ENTITYTABLE + "', " +
                       "e.SecurityTable AS '" + Constants.ENTITY_SECURITYTABLE + "', " +
                       "e.HierarchyTable AS '" + Constants.ENTITY_HIERARCHYTABLE + "', " +
                       "e.HierarchyParentTable AS '" + Constants.ENTITY_HIERARCHYPARENTTABLE + "', " +
                       "e.CollectionTable AS '" + Constants.ENTITY_COLLECTIONTABLE + "', " +
                       "e.CollectionMemberTable AS '" + Constants.ENTITY_COLLECTIONMEMBERTABLE + "', ");
            if(result)
            {
                sqlQuery += "e.StagingBase AS '" + Constants.ENTITY_STAGINGBASE + "', " +
                            "cgi.Seed AS '" + Constants.ENTITY_CODEGENSEED + "', ";

            }

            sqlQuery += "e.IsBase AS '" + Constants.ENTITY_ISBASE + "', " +
                                      "e.IsFlat AS '" + Constants.ENTITY_ISFLAT + "', " +
                                      "a.ID AS '" + Constants.ATTRIBUTE_ID + "', " +
                                      "a.Name AS '" + Constants.ATTRIBUTE_NAME + "', " +
                                      "a.SortOrder AS '" + Constants.ATTRIBUTE_SORTORDER + "', " +
                                      "a.IsSystem AS '" + Constants.ATTRIBUTE_ISSYSTEM + "', " +
                                      "a.MemberType_ID AS '" + Constants.ATTRIBUTE_MEMBERTYPEID + "', " +
                                      "CAST(a.MUID AS NVARCHAR(50)) AS '" + Constants.ATTRIBUTE_MUID + "', " +
                                      "a.Entity_ID AS '" + Constants.ATTRIBUTE_ENTITYID + "', " +
                                      "e.Name AS '" + Constants.ATTRIBUTE_ENTITYNAME + "', " +
                                      "a.DisplayName AS '" + Constants.ATTRIBUTE_DISPLAYNAME + "', " +
                                      "a.TableColumn AS '" + Constants.ATTRIBUTE_TABLECOLUMN + "', " +
                                      "a.AttributeType_ID AS '" + Constants.ATTRIBUTE_ATTRIBUTETYPEID + "', " +
                                      "a.DataType_ID AS '" + Constants.ATTRIBUTE_DATATYPEID + "', " +
                                      "a.DataTypeInformation AS '" + Constants.ATTRIBUTE_DATATYPEINFORMATION + "', " +
                                      "a.InputMask_ID AS '" + Constants.ATTRIBUTE_INPUTMASKID + "', " +
                                      "a.DisplayWidth AS '" + Constants.ATTRIBUTE_DISPLAYWIDTH + "', " +
                                      "a.DomainEntity_ID AS '" + Constants.ATTRIBUTE_DOMAINENTITYID + "', " +
                                      "aDomain.Name AS '" + Constants.ATTRIBUTE_DOMAINENTITYNAME + "', " +
                                      "CAST(aDomain.MUID AS NVARCHAR(50)) AS '" + Constants.ATTRIBUTE_DOMAINENTITYMUID + "', " +
                                      "a.ChangeTrackingGroup AS '" + Constants.ATTRIBUTE_CHANGETRACKINGGROUP + "', " +
                                      "a.IsCode AS '" + Constants.ATTRIBUTE_ISCODE + "', " +
                                      "a.IsName AS '" + Constants.ATTRIBUTE_ISNAME + "', " +
                                      "a.IsReadOnly AS '" + Constants.ATTRIBUTE_ISREADONLY + "', " +
                                      "a.AttributeValidation_ID AS '" + Constants.ATTRIBUTE_ATTRIBUTEVALIDATIONID + "', " +
                                      "a.IsRequired AS '" + Constants.ATTRIBUTE_ISREQUIRED + "' " +
                                      "FROM mdm.tblModel m " +
                                      "INNER JOIN mdm.tblEntity e ON m.ID = e.Model_ID " +
                                      "INNER JOIN mdm.tblAttribute a ON e.ID = a.Entity_ID " +
                                      "LEFT JOIN mdm.tblEntity aDomain ON aDomain.ID = a.DomainEntity_ID ";
             if(result)
             {
                 sqlQuery += "LEFT JOIN mdm.tblCodeGenInfo cgi ON cgi.EntityID = e.ID ";
             }

            sqlQuery += String.Format("WHERE m.Name = '{0}' " +
                                      "ORDER BY m.Name, e.Name, a.SortOrder", server.Model);
            
            //Get Model details
            sqlQuery += String.Format(" SELECT DISTINCT m.Name AS '" + Constants.MODEL_NAME + "', " +
                       "e.Name AS '" + Constants.ENTITY_NAME + "', " +
                       "CAST(e.MUID AS NVARCHAR(50)) AS '" + Constants.ENTITY_MUID + "', " +
                       "sv.Name AS '" + Constants.VIEW_NAME + "', " +
                       "sv.ViewFormat_ID AS '" + Constants.VIEW_VIEWFORMATID + "', " +
                       "sv.DerivedHierarchy_ID AS '" + Constants.VIEW_DERIVEDHIERARCHYID + "', " +
                       "sv.Levels AS '" + Constants.VIEW_LEVELS + "', " +
                       "sv.IsDirty AS '" + Constants.VIEW_ISDIRTY + "', " +
                       "CASE WHEN (it.TABLE_NAME IS NOT NULL) THEN 1 " +
                       "ELSE 0 " +
                       "END AS '" + Constants.VIEW_PHYSICALLYEXISTS + "' " +
                       "FROM mdm.tblModel m " +
                       "INNER JOIN mdm.tblEntity e ON m.ID = e.Model_ID " +
                       "INNER JOIN mdm.tblSubscriptionView sv ON m.ID = sv.Model_ID AND e.ID = sv.Entity_ID " +  
                       "LEFT JOIN INFORMATION_SCHEMA.TABLES it ON it.TABLE_SCHEMA = 'mdm' AND it.TABLE_NAME = sv.Name AND it.TABLE_TYPE = 'VIEW' " +
                       "WHERE m.Name = '{0}' " +
                       "ORDER BY m.Name, e.Name, sv.Name", server.Model);

             sqlQuery += String.Format(" SELECT DISTINCT m.Name AS '" + Constants.MODEL_NAME + "', " +
                    "e.Name AS '" + Constants.ENTITY_NAME + "', " +
                    "CAST(e.MUID AS NVARCHAR(50)) AS '" + Constants.ENTITY_MUID + "', " +
                    "ag.Name AS '" + Constants.ATTRIBUTEGROUP_NAME + "', " +
                    "CAST(ag.MUID AS NVARCHAR(50)) AS '" + Constants.ATTRIBUTEGROUP_MUID + "', " +
                    "ag.SortOrder AS '" + Constants.ATTRIBUTEGROUP_SORTORDER + "' " +
                    "FROM [mdm].[tblModel] m " +
                    "INNER JOIN [mdm].[tblEntity] e ON e.Model_ID = m.ID " +
                    "INNER JOIN [mdm].[tblAttribute] a ON a.Entity_ID = e.ID " +
                    "INNER JOIN mdm.tblAttributeGroup ag ON e.ID = ag.Entity_ID " +
                    "WHERE m.Name = '{0}' " +
                    "ORDER BY m.Name, e.Name, ag.SortOrder", server.Model);


            sqlQuery += String.Format(" SELECT DISTINCT m.Name AS '" + Constants.MODEL_NAME + "', " +
                    "e.Name AS '" + Constants.ENTITY_NAME + "', " +
                    "CAST(e.MUID AS NVARCHAR(50)) AS '" + Constants.ENTITY_MUID + "', " +
                    "a.Name AS '" + Constants.ATTRIBUTE_NAME + "', " +
                    "CAST(a.MUID AS NVARCHAR(50)) AS '" + Constants.ATTRIBUTE_MUID + "', " +
                    "a.MemberType_ID AS '" + Constants.ATTRIBUTE_MEMBERTYPEID + "', " +
                    "ag.Name AS '" + Constants.ATTRIBUTEGROUP_NAME + "', " +
                    "CAST(ag.MUID AS NVARCHAR(50)) AS '" + Constants.ATTRIBUTEGROUP_MUID + "', " +
                    "ag.SortOrder AS '" + Constants.ATTRIBUTEGROUP_SORTORDER + "', " +
                    "CAST(agd.MUID AS NVARCHAR(50)) AS '" + Constants.ATTRIBUTEGROUPDETAIL_MUID + "', " +
                    "agd.SortOrder AS '" + Constants.ATTRIBUTEGROUPDETAIL_SORTORDER + "', " +
                    "agd.Attribute_ID AS '" + Constants.ATTRIBUTE_ID + "' " +
                    "FROM [mdm].[tblModel] m " +
                    "INNER JOIN [mdm].[tblEntity] e ON e.Model_ID = m.ID " +
                    "INNER JOIN [mdm].[tblAttribute] a ON a.Entity_ID = e.ID " +
                    "INNER JOIN mdm.tblAttributeGroupDetail agd ON agd.Attribute_ID = a.ID " +
                    "INNER JOIN mdm.tblAttributeGroup ag ON ag.ID = agd.AttributeGroup_ID " +
                    "WHERE m.Name = '{0}' " +
                    "ORDER BY m.Name, e.Name, ag.SortOrder, agd.SortOrder", server.Model);

            return GetData(sqlQuery, server);
        }

        static public DataSet GetMdsSystemDetails(ServerMDSDetails server)
        {
            string sqlQuery = String.Format(" SELECT DISTINCT ProductName AS '" + Constants.SYSTEM_PRODUCTNAME + "', " +
                                      "ProductVersion AS '" + Constants.SYSTEM_PRODUCTVERSION + "', " +
                                      "ProductRegistrationKey AS '" + Constants.SYSTEM_PRODUCTREGISTRATIONKEY + "', " +
                                      "SchemaVersion AS '" + Constants.SYSTEM_SCHEMAVERSION + "' " +
                                      "FROM mdm.tblSystem");

            return GetData(sqlQuery, server);
        }

        static public DataSet GetDatabases(string server)
        {
            string sqlQuery = "CREATE TABLE #FindMDMDatabases(databaseName SYSNAME) exec sp_msforeachdb 'USE[?] INSERT #FindMDMDatabases select DISTINCT db_name() from INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ''mdm'' AND TABLE_NAME = ''tblSystem''' SELECT DISTINCT databaseName FROM #FindMDMDatabases DROP TABLE #FindMDMDatabases";

            return GetData(sqlQuery, server, "msdb");
        }

        static public DataSet GetModels(string server,string database)
        {
            string sqlQuery = "SELECT DISTINCT Name FROM mdm.tblModel";

            return GetData(sqlQuery, server, database);
        }
        

        static public DataSet GetModelVersions(string server,string database, string model)
        {   
            string sqlQuery = String.Format("SELECT DISTINCT mv.Name FROM mdm.tblModel m INNER JOIN mdm.tblModelVersion mv ON m.ID = mv.Model_ID WHERE m.Name = '{0}'", model);
         
            return GetData(sqlQuery, server, database);
        }

        static public DataSet GetData(string query, ServerMDSDetails server)
        {
            return GetData(query, server.ServerName, server.Database);
        }

        public static DataSet CheckServiceBroker(ServerMDSDetails server)
        {
            return CheckServiceBroker(server.ServerName, server.Database);
        }

        static public DataSet CheckServiceBroker(string server, string database)
        {
            string sqlQuery = String.Format("SELECT is_broker_enabled FROM master.sys.databases WHERE name = '{0}'", database);
         
            return GetData(sqlQuery, server, database);
        }

        public static DataSet CheckCLR(ServerMDSDetails server)
        {
            return CheckCLR(server.ServerName, server.Database);
        }

        static public DataSet CheckCLR(string server, string database)
        {
            return GetData("SELECT ISNULL(CAST(value AS BIT),0) AS 'IsCLREnabled' FROM master.sys.configurations WHERE name = 'clr enabled'", server, database);
        }

        static public DataSet GetData(string query, string server, string database)
        {
            DataSet ds = new DataSet();

            string connStr = "Data Source=" + server + ";Initial Catalog=" + database + ";Integrated Security=SSPI;Connect Timeout=0";

            using (SqlConnection sqlConn = new SqlConnection(connStr))
            {
                if (SqlConnOnInfoMessage != null)
                {
                    sqlConn.InfoMessage += OnSqlConnOnInfoMessage;
                }

                command = new SqlCommand(query, sqlConn);
                command.CommandTimeout = 0;

                SqlDataAdapter dataAdapt = new SqlDataAdapter();
                dataAdapt.SelectCommand = command;

                dataAdapt.Fill(ds);

                sqlConn.Close();
            }

            return ds;
        }

        static public void RunQuery(string query, string server, string database)
        {
            string connStr = "Data Source=" + server + ";Initial Catalog=" + database + ";Integrated Security=SSPI;Connect Timeout=0";

            using (SqlConnection sqlConn = new SqlConnection(connStr))
            {
                if (SqlConnOnInfoMessage != null)
                {
                    sqlConn.InfoMessage += OnSqlConnOnInfoMessage;
                }
                SqlCommand command = new SqlCommand(query, sqlConn);
                command.CommandTimeout = 0;
                sqlConn.Open();
                command.ExecuteNonQuery();

                sqlConn.Close();
            }
        }


        public static event SqlInfoMessageEventHandler SqlConnOnInfoMessage; 
        
        private static void OnSqlConnOnInfoMessage(object sender, SqlInfoMessageEventArgs e)
        {
            if (SqlConnOnInfoMessage != null)
            {
                SqlConnOnInfoMessage(sender, e);
            }
        }
#region String
        public static string dataCellStringConv(SqlDataReader dr, string ColumnName)
        {
            return dr[ColumnName] == System.DBNull.Value ? null : dr[ColumnName].ToString();
        }
        public static string dataCellStringConv(DataRowView dr, string ColumnName)
        {
            return dr[ColumnName] == System.DBNull.Value ? null : dr[ColumnName].ToString();
        }
        public static string dataCellStringConv(DataRow dr, string ColumnName)
        {
            return dr[ColumnName] == System.DBNull.Value ? null : dr[ColumnName].ToString();
        }
#endregion
        #region int
        public static int? dataCellNullableIntConv(SqlDataReader dr, string ColumnName)
        {
            return dr[ColumnName] == System.DBNull.Value ? null : ((int?)(Convert.ToInt32(dr[ColumnName])));
        }
        public static int? dataCellNullableIntConv(DataRowView dr, string ColumnName)
        {
            return dr[ColumnName] == System.DBNull.Value ? null : ((int?)(Convert.ToInt32(dr[ColumnName])));
        }
        public static int? dataCellNullableIntConv(DataRow dr, string ColumnName)
        {
            return dr[ColumnName] == System.DBNull.Value ? null : ((int?)(Convert.ToInt32(dr[ColumnName])));
        }

        public static int dataCellIntConv(SqlDataReader dr, string ColumnName)
        {
            return Convert.ToInt32(dr[ColumnName]);
        }
        public static int dataCellIntConv(DataRowView dr, string ColumnName)
        {
            return Convert.ToInt32(dr[ColumnName]);
        }
        public static int dataCellIntConv(DataRow dr, string ColumnName)
        {
            return Convert.ToInt32(dr[ColumnName]);
        }
        #endregion
        #region bool
        public static bool? dataCellNullableBoolConv(DataRow dr, string ColumnName)
        {
            return dr[ColumnName] == System.DBNull.Value ? null : ((bool?)(Convert.ToBoolean(dr[ColumnName])));
        }

        public static bool dataCellBoolConv(DataRow dr, string ColumnName)
        {
            return Convert.ToBoolean(dr[ColumnName]);
        }
        #endregion

        public static DataSet CheckDatabaseExists(string Server, string DatabaseName)
        {
            string query = "SELECT name FROM master.dbo.sysdatabases WHERE name = '" + DatabaseName + "'";
            return GetData(query, Server, "master");
        }

        internal static DataTable CheckIfReplication(string Server, string DatabaseName)
        {
            string query = "DECLARE @DatabaseName NVARCHAR(200)" + Environment.NewLine +
                           "DECLARE @SqlQuery NVARCHAR(MAX)" + Environment.NewLine +
                           "IF EXISTS(SELECT *  FROM SYS.DATABASES WHERE is_published = 1 or is_subscribed = 1 or is_merge_published = 1)" + Environment.NewLine +
                           "BEGIN" + Environment.NewLine +
                           "	SELECT @DatabaseName = name FROM sys.databases WHERE is_distributor = 1" + Environment.NewLine +
                           "	SET @SqlQuery = N'exec [' + @DatabaseName + '].sys.sp_replmonitorhelpsubscription @publication_type = 0'" + Environment.NewLine +
                           "	EXEC sp_executesql @SqlQuery" + Environment.NewLine +
                           "END";


            DataSet ds = GetData(query, Server, "master");
            if(ds!= null && ds.Tables.Count > 0)
            {
                var result = from r in ds.Tables[0].AsEnumerable()
                             where r.Field<string>(Constants.REPLICATION_SUBSCRIBER_DB) == DatabaseName
                             select r;
                if (result.Count() > 0)
                {
                    return result.CopyToDataTable();
                }
            }
            return null;
        }
    }
}

